package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.fxmargin.bo.FxMarginPreOrderBO;
import com.kcs.fxmargin.model.TPreOrder;

public class PreOrderDao {
	
	public List<FxMarginPreOrderBO> searchPreOrder(Connection conn, PreparedStatement pstm, ResultSet rs, String cifNo) throws Exception{
		
		StringBuffer sql 							= new StringBuffer();
		List<FxMarginPreOrderBO> preOrderList	 	= new ArrayList<FxMarginPreOrderBO>();
		FxMarginPreOrderBO preOrder 				= null;
		
		sql.append(" SELECT c.cif_no, ");
		sql.append("        c.cust_name, ");
		sql.append("        p.cust_info_id, ");
		sql.append("        p.order_no, ");
		sql.append("        p.amount, ");
		sql.append("        p.ccy1, ");
		sql.append("        p.ccy2, ");
		sql.append("        p.exch_rate fx_rate, ");
		sql.append("        cast(p.update_dt AS date) as date, ");
		sql.append("        u.user_id update_by, ");
		sql.append("        u.user_name update_by_full_name, ");
		sql.append("        p.id, ");
		sql.append("        p.row_version ");
		sql.append(" FROM t_pre_order p ");
		sql.append(" LEFT JOIN m_users u ON p.update_by = u.user_id, ");
		sql.append("                        m_cust_info c ");
		sql.append(" WHERE p.active_status = 'A' ");
		sql.append("   AND c.active_status = 'A' ");
		sql.append("   AND p.cust_info_id = c.id ");
		
		if(cifNo != null && !"".equals(cifNo)) sql.append(" and c.cif_no = ? ");
		
		System.out.println("SQL -> " + sql.toString());
		
		int index = 1;
		pstm = conn.prepareStatement(sql.toString());
		
		if(cifNo != null && !"".equals(cifNo)){
			pstm.setString(index, cifNo);
			index++;
		}
		
		rs = pstm.executeQuery();
		index = 1;
		
		while(rs.next()){
			preOrder = new FxMarginPreOrderBO();
			preOrder.setRowNum(index);
			preOrder.setCifNo(rs.getString("cif_no"));
			preOrder.setCustomerName(rs.getString("cust_name"));
			preOrder.setCustInfoId(rs.getString("cust_info_id"));
			preOrder.setOrderNo(rs.getString("order_no"));
			preOrder.setAmount(rs.getBigDecimal("amount"));
			preOrder.setCcy1(rs.getString("ccy1"));
			preOrder.setCcy2(rs.getString("ccy2"));
			preOrder.setFxRate(rs.getBigDecimal("fx_rate"));
			preOrder.setDate(rs.getDate("date"));
			preOrder.setUpdateBy(rs.getString("update_by"));
			preOrder.setUpdateByFullName(rs.getString("update_by_full_name"));
			preOrder.setId(rs.getString("id"));
			preOrder.setJsonObject();
			
			preOrderList.add(preOrder);
			index++;
		}
		
		System.out.println("preOrderList size = " + preOrderList.size());
		
		return preOrderList;
	}
	
	public String insert(Connection conn, PreparedStatement pstm, TPreOrder preOrder) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int insert = 0;
		
		sql.append(" INSERT INTO t_pre_order (id, cust_info_id, order_no, amount, ccy1, ccy2,  ");
		sql.append(" exch_rate, active_status, create_by, create_dt, update_by, update_dt, row_version) ");
		sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, getDate(), ?, getDate(), ?) ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, preOrder.getId());
		pstm.setString(2, preOrder.getCustInfoId());
		pstm.setString(3, preOrder.getOrderNo());
		pstm.setBigDecimal(4, preOrder.getAmount());
		pstm.setString(5, preOrder.getCcy1());
		pstm.setString(6, preOrder.getCcy2());
		pstm.setBigDecimal(7, preOrder.getExchRate());
		pstm.setString(8, preOrder.getActiveStatus());
		pstm.setString(9, preOrder.getCreateby());
		pstm.setString(10, preOrder.getUpdateBy());
		pstm.setInt(11, 1);
		
		insert = pstm.executeUpdate();
		
		System.out.println("insert = " + insert);
		
		return insert == 1 ? "s" : "e";
	}
	
	public String update(Connection conn, PreparedStatement pstm, TPreOrder preOrder) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int update = 0;
		
		sql.append(" update t_pre_order set  ");
		sql.append(" amount = ?, ");
		sql.append(" ccy1 = ?, ");
		sql.append(" ccy2 = ?,  ");
		sql.append(" exch_rate = ?, ");
		sql.append(" update_by = ?, ");
		sql.append(" update_dt = getDate(), ");
		sql.append(" row_version = ? ");
		sql.append(" where id = ? "); 	
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setBigDecimal(1, preOrder.getAmount());
		pstm.setString(2, preOrder.getCcy1());
		pstm.setString(3, preOrder.getCcy2());
		pstm.setBigDecimal(4, preOrder.getExchRate());
		pstm.setString(5, preOrder.getUpdateBy());
		pstm.setInt(6, preOrder.getRowVersion());
		pstm.setString(7, preOrder.getId());
		
		update = pstm.executeUpdate();
		
		System.out.println("update = " + update);
		
		return update == 1 ? "s" : "e";
	}
	
	public String delete(Connection conn, PreparedStatement pstm, String id) throws Exception {
		
		StringBuffer sql 		= new StringBuffer();
		
		int delete = 0;
		
		sql.append(" delete from t_pre_order where id = ? ");
		
		System.out.println("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, id);
		delete = pstm.executeUpdate();
		
		System.out.println("delete = " + delete);
		
		return delete == 1 ? "s" : "e";
	}
}
